import pymysql
import xlsxwriter
import datetime

sheet_time = datetime.datetime.now()
sheet_mark = sheet_time.strftime('%Y-%m-%d')
book_mark = sheet_time.strftime('%Y%m%d%H%M')

Table_PPTN = 'st_pptn_r'
Table_RIVER = 'st_river_r'

# 雨量查询
cmd_rain = '''
SELECT
a.addvcdId,
a.stationId,
b.STMNM,
a.TM,
a.DYP
FROM
{} a
LEFT JOIN st_stbprp_b b ON a.stationId = b.stationId
AND a.addvcdId = b.addvcdId
WHERE
a.TM >= '2017-05-01 00:00:00'
AND a.TM < '2017-06-01 00:00:00'
AND a.TM LIKE '%:00:00'
AND a.addvcdId = 4209
AND b.STMNM IS NOT NULL
ORDER BY
a.TM DESC
'''.format(Table_PPTN)

# 水位查询
cmd_river = '''
SELECT
a.addvcdId,
a.stationId,
b.STMNM,
a.TM,
a.Z
FROM
{} a
LEFT JOIN st_stbprp_b b ON a.stationId = b.stationId
AND a.addvcdId = b.addvcdId
WHERE
a.TM >= '2017-05-01 00:00:00'
AND a.TM < '2017-06-01 00:00:00'
AND a.TM LIKE '%:00:00'
AND a.addvcdId = 4209
AND b.STMNM IS NOT NULL
ORDER BY
a.TM DESC
'''.format(Table_RIVER)

# 测站查询
cmd_station_info = '''
SELECT STCD,addvcdId,stationId,STMNM,PHONE,STTP
FROM
st_stbprp_b
ORDER BY
stationId ASC
'''


def get_connection():
    """
    获取数据链接
    :return: 数据源连接
    """
    conn = pymysql.connect(host='192.168.1.100', port=3306, user='root', passwd='summit-1', db='ucplite_xiaogan',
                           charset='utf8')
    return conn


def get_data(cmd):
    """
    获取mysql雨量数据
    :return:
    """
    conn = get_connection()
    cur = conn.cursor()
    cur.execute(cmd)
    result = cur.fetchall()

    cur.close()
    conn.close()
    return result


def get_col_data(cmd):
    """
    获取列名
    :return:列明
    """
    conn = get_connection()
    cur = conn.cursor()
    cur.execute(cmd)
    fields = cur.description

    cur.close()
    conn.close()
    return fields


def write_to_excel(result, fields, table_name):
    workbook = xlsxwriter.Workbook('孝感2017年5月份{}数据_导出日期'.format(table_name) + book_mark + '.xlsx')
    worksheet = workbook.add_worksheet(sheet_mark)
    bold = workbook.add_format({'bold': True})
    for field in range(0, len(fields)):
        worksheet.write(0, field, fields[field][0], bold)
    for row in range(1, len(result) + 1):
        for col in range(0, len(fields)):
            worksheet.write(row, col, u'%s' % result[row - 1][col])

    workbook.close()


if __name__ == '__main__':
    result_rain = get_data(cmd_rain)
    fields_rain = get_col_data(cmd_rain)
    write_to_excel(result_rain, fields_rain, '雨量')

    result_river = get_data(cmd_river)
    fields_river = get_col_data(cmd_river)
    write_to_excel(result_river, fields_river, '水位')

    result_station = get_data(cmd_station_info)
    fields_station = get_col_data(cmd_station_info)
    write_to_excel(result_station, fields_station, '测站')
